﻿using Perfolizer.Horology;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OrmTest
{
    internal class _7_GroupQuery
    {
        public static void Init()
        {
            var db = DbHelper.GetNewDb();
            List<Student> students = new List<Student>
            {
                new Student { Id = 1, Name = "Alice", Age = 20,ChushengDate=DateTime.Now.AddDays(-1).AddHours(-7) },
                new Student { Id = 2, Name = "Bob", Age = 22,ChushengDate=DateTime.Now.AddDays(-2).AddHours(-5) },
                new Student { Id = 3, Name = "Alice", Age = 21 ,ChushengDate=DateTime.Now.AddDays(-4)},
                new Student { Id = 4, Name = "Charlie", Age = 19 ,ChushengDate=DateTime.Now.AddDays(-15)},
                new Student { Id = 5, Name = "Charlie2", Age = 19 ,ChushengDate=DateTime.Now.AddDays(-16)},
                new Student { Id = 6, Name = "Charlie3", Age = 19 ,ChushengDate=DateTime.Now.AddDays(-7).AddHours(+5)},
                new Student { Id = 7, Name = "Charlie4", Age = 19 ,ChushengDate=DateTime.Now.AddDays(-8).AddHours(+10)},
                new Student { Id = 8, Name = "Bob", Age = 20 ,ChushengDate=DateTime.Now.AddDays(-9).AddHours(+15)}
            };
             
            // 初始化数据库表结构，如果表不存在则创建 (Initialize database table structure; create if not exists)
             db.CodeFirst.InitTables<Student>();

            // 清空指定表中的所有数据 (Truncate all data in the specified table)
            db.DbMaintenance.TruncateTable<Student>();

            //插入记录(Insert datas)
            db.Insertable(students).ExecuteCommand();

            foreach (var student in students.OrderBy(s=>s.ChushengDate))
            {
                Console.WriteLine(student.ToString());
            }

            //
            var hour = TimeStampConverter.LocalTimeZoneHours();

            var timeIntervals = "yyyy-mm-dd hh24";

            var result = db.Queryable<Student>()
            .GroupBy(s => s.ChushengDate.AddHours(8).ToString(timeIntervals))
            .Select(g => new StudentDTO
            {
                ChushengDateString = g.ChushengDate.ToString(timeIntervals),
            })
            .ToList();

            //var result = db.Queryable<Student>()
            //.GroupBy(s => SqlFunc.MappingColumn(s.ChushengDate,
            //       $"TO_CHAR(ChushengDate + INTERVAL '{hour} hours', '{timeIntervals}')"))
            //.Select(s => new StudentDTO
            //{
            //    ChushengDateString = SqlFunc.MappingColumn<string>(
            //   $"TO_CHAR(ChushengDate + INTERVAL '{hour} hours', '{timeIntervals}')")
            //})
            //.ToList().OrderBy(s=>s.ChushengDateString);;


            foreach (var student in result)
            {
                Console.WriteLine(student.ToString());
            }

            // 分组查询示例 (Grouping Query Example)
            //var groupedStudents = db.Queryable<Student>()
            //                         .GroupBy(s => s.Name)
            //                         .Select(g => new
            //                         {
            //                             Name = g.Name, // 学生姓名 (Student Name)
            //                             Count = SqlFunc.AggregateCount(g.Id), // 学生数量 (Count of Students)
            //                             AverageAge = SqlFunc.AggregateAvg(g.Age), // 平均年龄 (Average Age)
            //                             MaxAge = SqlFunc.AggregateMax(g.Age), // 最大年龄 (Maximum Age)
            //                             MinAge = SqlFunc.AggregateMin(g.Age) // 最小年龄 (Minimum Age)
            //                         })
            //                         .ToList();


            // 去重查询示例 (Distinct Query Example)
            var distinctNames = students.Select(s => s.Name).Distinct().ToList();
             
        }

        [SqlSugar.SugarTable("Student07")]
        public class Student
        {
            [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
            public int Id { get; set; } // 学生ID (Student ID)
            public string Name { get; set; } // 学生姓名 (Student Name)
            public int Age { get; set; } // 学生年龄 (Student Age)
            public DateTime ChushengDate { get; set; } // 学生年龄 (Student Age)

            public override string ToString()
            {
                return $"UTC的日期: {ChushengDate}";
            }
        }

        public class StudentDTO
        {
            [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
            public int Id { get; set; } // 学生ID (Student ID)
            public string Name { get; set; } // 学生姓名 (Student Name)
            public int Age { get; set; } // 学生年龄 (Student Age)
            public string ChushengDateString { get; set; } // 学生年龄 (Student Age)


            public override string ToString()
            {
                return $"转换后的日期: {ChushengDateString}";
            }
        }
    }

    public static class TimeStampConverter
    {

        /// <summary>
        /// utc时间转换成时间戳-毫秒级
        /// </summary>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public static ulong UtcTimeToTimeStamp(DateTime dateTime)
        {
            return (ulong)(dateTime - DateTime.UnixEpoch).TotalMilliseconds;
        }
        /// <summary>
        /// utc时间转换成时间戳-毫秒级
        /// 数据库保存02日8点--》Dapper读取后02日0点--》UtcTimeToTimeStamp(dateTime)后02日8点--》该方法再加当前时区时间成02日16点
        /// </summary>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public static ulong UtcTimeDBToTimeStamp(DateTime dateTime)
        {
            if (dateTime <= DateTime.UnixEpoch) return 0;
            return UtcTimeToTimeStamp(dateTime) + (ulong)TimeZoneInfo.Local.GetUtcOffset(DateTime.Now).TotalMilliseconds;
        }

        /// <summary>
        /// 时间戳转换成Utc时间-毫秒级
        /// </summary>
        /// <param name="timeStamp"></param>
        /// <returns></returns>
        public static DateTime TimeStampToUtcTime(ulong timeStamp)
        {
            return DateTime.UnixEpoch.Add(new TimeSpan(long.Parse(timeStamp.ToString().PadRight(17, '0'))));
        }

        /// <summary>
        /// 时间戳转化成LocalTime
        /// </summary>
        /// <param name="timeStamp"></param>
        /// <returns></returns>
        public static DateTime TimeStampToLocalTime(ulong timeStamp)
        {
            return TimeStampToUtcTime(timeStamp + (ulong)TimeZoneInfo.Local.GetUtcOffset(DateTime.Now).TotalMilliseconds);
        }

        /// <summary>
        /// 时间戳转换成Utc时间写入数据库-毫秒级，dapperbug暂时用这个
        /// </summary>
        /// <param name="timeStamp"></param>
        /// <returns></returns>
        public static DateTime TimeStampToUtcTimeDB(ulong timeStamp)
        {
            return DateTime.SpecifyKind(TimeStampToUtcTime(timeStamp), DateTimeKind.Unspecified);
        }

        /// <summary>
        /// 数据库中的Utc时间转换成真正的utc时间，dapperbug读取上来的utc时间少8小时，暂时未发现问题这么用。
        /// </summary>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public static DateTime UtcTimeDBToUtcTime(DateTime dateTime)
        {
            //DateTimeOffset chinaTime = TimeZoneInfo.ConvertTime(newt, chinaTimeZone); 
            return dateTime.Add(TimeZoneInfo.Local.GetUtcOffset(DateTime.Now));
        }

        /// <summary>
        /// 数据库Utc时间转换成本地时间，暂时这么用
        /// </summary>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public static DateTime UtcTimeDBToLocalTime(DateTime dateTime)
        {
            return UtcTimeDBToUtcTime(dateTime).Add(TimeZoneInfo.Local.GetUtcOffset(DateTime.Now));
        }

        /// <summary>
        /// 将Utc时间Kind由Utc转换为Unspecified，解决dapper默认加时区
        /// </summary>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public static DateTime UtcTimeKindToUnspecified(DateTime utcTime)
        {
            return DateTime.SpecifyKind(utcTime.ToUniversalTime(), DateTimeKind.Unspecified);
        }
        /// <summary>
        /// 返回当前时区小时
        /// </summary>
        /// <returns></returns>
        public static string LocalTimeZoneHours()
        {
            return TimeZoneInfo.Local.BaseUtcOffset.Hours.ToString();
        }
    }
}
